package MyHelper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
/**
 * This class make connection and quering MySQL database simpler.
 * User first need to create an object with: hostname, database name, username and password.
 * Then use this object to get the result of  queries
 * 
 *  
 * @author VuHa
 *
 */
public class MYSQLDatabase {
	private String hostname="genome-mysql.cse.ucsc.edu";
	private String db="";
	private int extendedRange = 100000;
	private int numberOfNeighbors = 5;
	
	private String url = "";
	private String user = "genome";
	private String password = "";
	
	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	
	private String query="";
	
	private String newline ="\n";
	
	public MYSQLDatabase(){
		this("genome-mysql.cse.ucsc.edu", "hg19",100000, 5);
	}
	
	public MYSQLDatabase(String hostname, String db){
		this(hostname, db,100000, 5);
	}
	
	public MYSQLDatabase(String hostname, String db, int extendedRange){
		this(hostname, db,extendedRange, 5);
	}
	
	public MYSQLDatabase(String hostname, String db, int extendedRange, int numberOfNeighbors){
		this.hostname=hostname;
		this.db=db;
		this.extendedRange=extendedRange;
		this.numberOfNeighbors = numberOfNeighbors;
		
		this.user = "genome";
		this.password = "";
		
		url = "jdbc:mysql://" + this.hostname + "/" + this.db + "?user=" + this.user
				+ "&password=" + this.password;
	}
	
	/**
	 * Execute input query and return result as a StringBuffer
	 * 
	 * @param q = query
	 * 
	 * @return a StringBuffer as result of the input query
	 */
	public StringBuffer queryToStringBuffer(String q){
		System.out.println("executing query: " + q);
		StringBuffer result = new StringBuffer();
		
		this.setQuery(q);
		this.createResultSet();
		if(rs!=null){
			try {
				
				//following lines try to put a gene in the form than can be used by LocusVu
				ResultSetMetaData rsMeta = rs.getMetaData();
				int nCol = rsMeta.getColumnCount();
				int chrCol = 0, txStartCol =0, txEndCol = 0;
				for (int i =1;i<=nCol;i++){
					if (rsMeta.getColumnLabel(i).equals("chrom"))
						chrCol = i;
					if (rsMeta.getColumnLabel(i).equals("txStart"))
						txStartCol = i;
					if (rsMeta.getColumnLabel(i).equals("txEnd"))
						txEndCol = i;
				}
				
				//this paragraph deal with data is information about gene that have position infor
				//this export the file that can read by locusvu
				
				if(chrCol!=0 && txStartCol!=0){
					while(rs.next()){
						for (int i=1;i<chrCol;i++){
							result.append(rs.getString(i)+"\t");
						}
						result.append(rs.getString(chrCol)+":"+rs.getString(txStartCol)+"-"+rs.getString(txEndCol)+"\t");
						
						for (int i=chrCol+1;i<=nCol;i++){
							if(i!=txStartCol && i!=txEndCol)
								result.append(rs.getString(i)+"\t");
						}
												
						result.append(newline);//newLine
					}
				}else{
					while(rs.next()){
						for (int i=1;i<=nCol;i++){
							result.append(rs.getString(i)+"\t");
						}
						result.append(newline);//newLine
					}
				}
				
				
				closeDB();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	/**
	 * Execute input query and return result as an Array of Strings
	 * 
	 * @param query = query that we want to execute
	 * 
	 * @return a String[] as result of the input query
	 */
	public String[] queryToStringArray(String query){
		String rString = new String(this.queryToStringBuffer(query));
		return rString.split(newline);
	}
	
	/**
	 * Execute input query and return result as an ArrayList of Strings
	 * @param query = query that we want to execute
	 * @return an ArrayList of Strings as result of input query
	 */
	public ArrayList<String> queryToArrayListString(String query){
		ArrayList<String> result = new ArrayList<String>();
		
		String[] sArray = queryToStringArray(query);
		
		for (String s:sArray){
			result.add(s);
		}
		return result;
	}
	
	/**
	 * Execute input query and write the result to file
	 * 
	 * @param query = query that we want to execute
	 * @param fileName = Name of the output file
	 * 
	 * @return void
	 */
	public void queryToFile(String query,String fileName){
		MyFile.writeToFile(fileName, this.queryToStringBuffer(query));
	}
	
	/**
	 * Register driver for MySQL
	 * 
	 * @return the state of driver registration
	 */
	private boolean register(){
		boolean state = false;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			//System.out.println("Driver registered");
			state = true;
			
		}catch(Exception ex){
			System.out.println("Cannot register Database Driver");
			state = false;
		}
		
		return state;
	}
	
	/**
	 * Create connection to database
	 * 
	 * @return state of creating connection to database (true = successful, false = not successful)
	 */
	private boolean createConnection(){
		boolean state = false;
		
		if(this.register()){
			try{
				conn = DriverManager.getConnection(url);
				//System.out.println("Connection created "+url);
				state = true;
			
			}catch(SQLException ex){
				System.out.println("Cannot create connection");
				System.out.println("SQLException: " +ex.getMessage());
				System.out.println("SQLState: " +ex.getSQLState());
				System.out.println("VendorError: " +ex.getErrorCode());
				state = false;
			}
		}
		
		return state;
	}
	/**
	 * try to execute an query an create ResultSet for that query
	 * 
	 */
	private void createResultSet(){
				
		if (this.createConnection()){
			try{
				this.stmt = conn.createStatement();
				if(this.stmt.execute(query));{
					this.rs = stmt.getResultSet();
					
				}
			}catch (SQLException ex){
				System.out.println("SQLException: " +ex.getMessage());
				System.out.println("SQLState: " +ex.getSQLState());
				System.out.println("VendorError: " +ex.getErrorCode());
			}
			
		}
		
	}
	/**
	 * Close statement and ResultSet after getting query's result
	 */
	private void closeDB(){
		if(rs !=null){
			try{
				rs.close();
			}catch (SQLException sqlEx){}
			rs = null;
		}
		if (stmt !=null){
			try{
				stmt.close();
			}catch (SQLException sqlEx){}
			stmt = null;
		}
	}
	
	/**
	 * 
	 * @return the current query 
	 */
	public String getQuery(){
		return this.query;
	}
	
	/**
	 * 
	 * @return hostname
	 */
	public String getHostname(){
		return this.hostname;
	}
	
	/**
	 * 
	 * @return database name of this object
	 */
	public String getDatabase(){
		return this.db;
	}
	
	/**
	 * 
	 * @return the range used to find neighbors
	 */
	public int getExtendedRange(){
		return this.extendedRange;
	}
	/**
	 * 
	 * @return maximum number of neighbors in up-stream and down-stream
	 */
	public int getNumberOfNeighbors(){
		return this.numberOfNeighbors;
	}
	//set methods
	/**
	 * set query of this object
	 * @param query to set to this object
	 */
	public void setQuery(String query){
		this.query=query;
	}
	/**
	 * Set hostname for this object
	 * @param hostname = new hostname for this object
	 */
	public void setHostname(String hostname){
		this.hostname = hostname;
	}
	
	/**
	 * Set tha database for this object
	 * @param database = name of new database to set
	 */
	public void setDatabase(String database){
		this.db = database;
	}
	/**
	 * Set the extend range for this object
	 * @param extendedRange an new integer value for range to search neighbors 
	 */
	public void setExtendedRange(int extendedRange){
		this.extendedRange = extendedRange;
	}
	
	/**
	 * Set the number of neighbors in up-and down direction
	 * @param numberOfNeighbots
	 */
	public void setNumberOfNeighbors(int numberOfNeighbots){
		this.numberOfNeighbors = numberOfNeighbots;
	}
}
